package com.qp.basics.estivate.jdbc;

import com.qp.basics.estivate.BaseDatabase;
import com.qp.basics.estivate.Column;
import com.qp.basics.estivate.Table;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 不特定数据库的Database接口实现。
 */
public abstract class DatabaseJdbc extends BaseDatabase {
	private static final Log logger = LogFactory.getLog(DatabaseJdbc.class);

	public DatabaseJdbc() {

	}

	public String getPostFix() {
		return "";
	}

	public String getPreFix() {
		return "";
	}

	public Table[] getTables() {
		if (tables != null)
			return tables;
		ResultSet rs = null;
		List v = new ArrayList();
		Connection conn = null;
		try {
			conn = connectionProvider.getConnection();
			rs = conn.getMetaData().getTables(null, this.tableSchema, null,
					new String[] { "TABLE" });
			if (rs == null) {
				return null;
			}
			int i = 0;
			while (rs.next()) {
				i++;
				Table table = new Table();
				table.setDatabase(this);
				Map properties = this.resultSetToMap(rs);
				table.putProperties(properties);
				table.setTableName(rs.getString("TABLE_NAME"));
				table.setTableType(rs.getString("TABLE_TYPE"));
				// String tableCat;//1
				// String tableSchem;//2
				// String tableName;//3
				// String tableType;//4
				// String remarks;//5
				// String type_cat;//6
				// String type_schem;//7
				// String typeName;//8
				// String self_referencing_col_name;//9
				// String ref_generation;//10
				// table.setTableCat(rs.getString("TABLE_CAT"));
				// table.setRemarks(rs.getString("REMARKS"));
				// if(rs.getString("TYPE_CAT") != null)
				// table.setType_cat(rs.getString("TYPE_CAT"));
				//
				// table.setType_schem(rs.getString("TYPE_SCHEM"));
				// table.setTypeName(rs.getString("TYPE_NAME"));
				// table.setSelf_referencing_col_name(rs.getString("SELF_REFERENCING_COL_NAME"));
				// table.setRef_generation(rs.getString("REF_GENERATION"));
				v.add(table);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			closeConn(conn);
		}
		tables = new Table[v.size()];
		v.toArray(tables);
		return tables;
	}

	public String getTableCreateSql(Table table) {
		StringBuffer createSql = new StringBuffer();
		createSql.append("CREATE TABLE " + table.getTableName() + " (");
		Column[] columns = table.getColumns();
		boolean isPrimaryKeyDone = false;
		// channel_name VARCHAR( 20 ) DEFAULT 'ds' NOT NULL AUTO_INCREMENT
		// COMMENT '注释' ,
		for (int i = 0; i < columns.length; i++) {
			if (i != 0)
				createSql.append(',');
			createSql.append("\n");
			Column col = columns[i];
			String colName = col.getColumnName();
			String dataType = col.getDataType();
			Object defaultValue = col.getDefaultValue();
			String columnSize = col.getColumnSize();
			String comment = col.getComment();
			createSql.append("" + colName + " " + dataType);
			if (columnSize != null) {
				createSql.append("(" + columnSize + ") ");
			}
			if (col.isIdAutoIncrement()) {
				createSql.append(" auto_increment");

			}
			if (defaultValue != null) {
				createSql.append(" DEFAULT '" + defaultValue + "'");
			}
			if (!col.isNullable()) {
				createSql.append(" NOT NULL ");
			}
			Object EXTRA = col.getExtra();
			if (EXTRA != null) {
				createSql.append(" " + EXTRA + " ");
			}
			// if(comment != null && !"".equals(comment))
			// createSql.append("COMMENT '"+comment+"'");
		}
		Column[] pks = table.getPks();
		if (pks != null && pks.length > 0) {
			createSql.append(',');
			createSql.append("\n");
			createSql.append("PRIMARY KEY ( ");
			for (int i = 0; i < pks.length; i++) {
				if (i != 0)
					createSql.append(',');
				Column col = pks[i];
				createSql.append("" + col.getColumnName() + "");
			}
			createSql.append(')');
		}
		createSql.append("\n");
		createSql.append(")");
		// if(table.getComment() != null){
		// createSql.append("COMMENT = '"+table.getComment()+"'");
		// }
		return createSql.toString();
	}

	public Column[] getColumns(Table table) {
		Connection conn = connectionProvider.getConnection();
		ResultSet rs = null;
		try {
			String tableName = table.getTableName();
			rs = conn.getMetaData().getColumns(null, null, tableName, "%");
			List v = new ArrayList();
			while (rs.next()) {
				Map properties = this.resultSetToMap(rs);
				Column column = new Column();
				column.setTable(table);
				column.putProperties(properties);

				column.setColumnName(rs.getString("COLUMN_NAME"));
				column.setDataType(rs.getString("DATA_TYPE"));
				column.setColumnSize(rs.getString("COLUMN_SIZE"));
				// String nullable = rs.getString("NULLABLE");
				// column.setNullable("YES".equals(rs.getString("IS_NULLABLE")));
				v.add(column);
			}
			rs.close();
			Column[] columns = new Column[v.size()];
			v.toArray(columns);
			rs = conn.getMetaData().getPrimaryKeys(null, null,
					tableName.toUpperCase());
			while (rs.next()) {
				String pkName = rs.getString("COLUMN_NAME");
				for (int i = 0; i < columns.length; i++) {
					String colName = columns[i].getColumnName();
					if (colName.equals(pkName)) {
						columns[i].setPk(true);
					}

				}
			}
			return columns;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + " tableSchema="
					+ this.getTableSchema() + " tableName="
					+ table.getTableName(), e);
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// ALTER TABLE channel COMMENT='aaaaaaaaaaaaaaa'
	// public void alterTable(Table table) {
	// TODO Auto-generated method stub

	// }

	public void alterColumn(Table table, String oldColumnName, Column column) {
		// ALTER TABLE channel CHANGE channelId channelId BIGINT( 20 ) DEFAULT
		// '0' NOT NULL
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String alterColumnSql = "ALTER TABLE "
				+ tableName
				+ " CHANGE "
				+ oldColumnName
				+ " "
				+ newColName
				+ " "
				+ column.getDataType()
				+ (column.getColumnSize() == null ? "" : "("
						+ column.getColumnSize() + ")")
				+ ((column.getDefaultValue() == null || "".equals(column
						.getDefaultValue())) ? "" : " DEFAULT '"
						+ column.getDefaultValue() + "'")
				+ (column.isNullable() ? "" : " NOT NULL ");

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void addColumn(Table table, Column column) {
		// ALTER TABLE table_name ADD field_name field_type;
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String extra = (String) column.getProperties().get("EXTRA");
		String alterColumnSql = "ALTER TABLE "
				+ tableName
				+ " ADD "
				+ newColName
				+ " "
				+ column.getDataType()
				+ (column.getColumnSize() == null ? "" : "("
						+ column.getColumnSize() + ")")
				+ ((column.getDefaultValue() == null || "".equals(column
						.getDefaultValue())) ? "" : "DEFAULT "
						+ column.getDefaultValue())
				+ (column.isNullable() ? "" : " NOT NULL ")
				+ (extra != null ? extra : "");

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void resetPrimaryKey(Table table, Column[] pks) {
		// ALTER TABLE channel DROP PRIMARY KEY ,
		// ADD PRIMARY KEY ( id , channel_name )
		String sql = "ALTER TABLE " + table.getTableName()
				+ " DROP PRIMARY KEY ";
		if (pks != null && pks.length > 0) {
			sql += ",ADD PRIMARY KEY (";
			for (int i = 0; i < pks.length; i++) {
				if (i != 0)
					sql += ',';
				Column col = pks[i];
				sql += col.getColumnName();
			}
			sql += ")";
		}
		executeUpdate(sql);

	}

	public String getDatabaseType() {
		return "Jdbc";
	}

	public void alterTableComment(String tableName, String comment) {
		String sql = "alter table " + tableName + " comment='" + comment + "'";
		this.executeUpdate(sql);
	}

	public void alterTableName(String oldTableName, String tableName) {
		String sql = "alter table " + oldTableName + " rename to " + tableName
				+ "";
		this.executeUpdate(sql);
	}

	public String[] getDataTypes() {
		return null;
	}
}
